package yongxin.controller.kingdeeReport;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.util.StringUtil;

import org.jeecgframework.tag.core.easyui.TagUtil;
import org.jeecgframework.tag.vo.datatable.SortDirection;
import org.jeecgframework.web.system.pojo.base.TSUser;
import org.jeecgframework.web.system.service.SystemService;

import yongxin.entity.annealer.AnnealerEntity;
import yongxin.entity.check.ContractEntity;
import yongxin.entity.check.ContractProcutEntity;
import yongxin.entity.processreport.ProcessReportEntity;
import yongxin.entity.temp_record.TempRecordEntity;
import yongxin.utils.ExcelUtils;

/**
 * @Title: Controller
 * @Description: 即时库存查询
 * @author zhangdaihao
 * @date 2015-02-05 14:03:13
 * @version V1.0
 * 
 */
@Controller
@RequestMapping("/inventoryController")
public class InventoryController extends BaseController
{
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(InventoryController.class);

	@Autowired
	private SystemService systemService;

	private String message;

	public String getMessage()
	{
		return message;
	}

	public void setMessage(String message)
	{
		this.message = message;
	}

	/**
	 * 
	 * 成品库即时库存列表
	 * 
	 * @return
	 */
	@RequestMapping(params = "c_inventoryList")
	public ModelAndView c_inventoryList(HttpServletRequest request)
	{

		return new ModelAndView("yongxin/kingdeeReport/c_inventoryList");

	}

	/**
	 * 
	 * 原材料即时库存列表
	 * 
	 * @return
	 */
	@RequestMapping(params = "y_inventoryList")
	public ModelAndView y_inventoryList(HttpServletRequest request)
	{

		return new ModelAndView("yongxin/kingdeeReport/y_inventoryList");

	}

	/**
	 * 
	 * 辅助材料即时库存列表
	 * 
	 * @return
	 */
	@RequestMapping(params = "f_inventoryList")
	public ModelAndView f_inventoryList(HttpServletRequest request)
	{

		return new ModelAndView("yongxin/kingdeeReport/f_inventoryList");

	}
    
	// 产成品库存月报表
	@RequestMapping(params = "inventoryByMonthList")
	public ModelAndView inventoryByMonthList(HttpServletRequest request)
	{

		return new ModelAndView("yongxin/kingdeeReport/inventoryByMonthList");

	}
	
	
	// 产成品入库月报表
	@RequestMapping(params = "inStockByMonthList")
	public ModelAndView inStockByMonthList(HttpServletRequest request)
	{

		return new ModelAndView("yongxin/kingdeeReport/inStockByMonthList");

	}
	
	// 机组耗用明细月报表
	@RequestMapping(params = "jzhyList")
	public ModelAndView jzhyList(HttpServletRequest request)
	{

		return new ModelAndView("yongxin/kingdeeReport/jzhyList");

	}
	
	// 专用备品备件购入耗用结存月报表
	@RequestMapping(params = "zybpbjList")
	public ModelAndView zybpbjList(HttpServletRequest request)
	{

		return new ModelAndView("yongxin/kingdeeReport/zybpbjList");

	}
	
	
	
	/**
	 * easyui 成品即时库存
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "c_datagrid")
	public void c_datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{

		String sql = " Select t1.FName as FItemName,t1.FModel,u1.FBatchNo, ";
		sql += " t2.FName as FStockName,t5.FName as FStockPlace,t4.FName as FUintName, ";
		sql += " Convert(decimal(18,4),u1.FQty/t4.FCoefficient) as FQty,t1.FNumber AS FItemNumber,dbo.fn_getmatterwidth(u1.FBatchNo) AS FWidth ";
		sql += " From RemoteServer.AIS2009.dbo.view_inventory u1 ";
		sql += " left join RemoteServer.AIS2009.dbo.t_ICItem t1 on u1.FItemID = t1.FItemID ";
		sql += " left join RemoteServer.AIS2009.dbo.t_Stock t2 on u1.FStockID=t2.FItemID ";
		sql += " left join RemoteServer.AIS2009.dbo.t_MeasureUnit t4 on t1.FStoreUnitID=t4.FMeasureUnitID ";
		sql += " left join RemoteServer.AIS2009.dbo.t_StockPlace t5 on u1.FStockPlaceID=t5.FSPID ";
		sql += " where (Round(u1.FQty,t1.FQtyDecimal)<>0 OR Round(u1.FQty/t4.FCoefficient,t1.FQtyDecimal)<>0) ";
		sql += " and t1.FDeleted=0  ";
		sql += " AND (t2.FNumber LIKE 'C.%' or t2.FNumber like 'EDP.%' or t2.FNumber like 'W.%' ) ";
		sql += " AND t2.FTypeID in (500,20291,20293) ";

		String FItemNumber = request.getParameter("FItemNumber");
		String FModel = request.getParameter("FModel");
		String FWidth = request.getParameter("FWidth");
		String FItemName = request.getParameter("FItemName");
		String FBatchNo = request.getParameter("FBatchNo");
		String FDCStockIDName = request.getParameter("FStockName");
		String FSPName = request.getParameter("FStockPlace");

		if (StringUtil.isNotEmpty(FItemNumber))
		{
			sql += " and t1.FNumber like '%" + FItemNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FModel))
		{
			sql += " and t1.Fmodel = " + "'" + FModel + "'";
		}
		if (StringUtil.isNotEmpty(FItemName))
		{
			sql += " and t1.Fname like " + "'%" + FItemName + "%'";
		}
		if (StringUtil.isNotEmpty(FWidth))
		{
			sql += " and dbo.fn_getmatterwidth(u1.FBatchNo) = " + "'" + FWidth + "'";
		}
		if (StringUtil.isNotEmpty(FBatchNo))
		{
			sql += " and u1.FBatchNo like " + "'%" + FBatchNo + "%'";
		}

		if (StringUtil.isNotEmpty(FDCStockIDName))
		{
			sql += " and t2.FName  = " + "'" + FDCStockIDName + "'";
		}
		if (StringUtil.isNotEmpty(FSPName))
		{
			sql += " and t5.FName  = " + "'" + FSPName + "'";
		}
        
		// 排序
		if(dataGrid.getSort() != null)
		{
			if(dataGrid.getSort().equals("FItemNumber")){

				 sql += " order by t1.FNumber "+dataGrid.getOrder();

				
			}else if(dataGrid.getSort().equals("FItemName")){

					sql += " order by t1.FName "+dataGrid.getOrder();

				
			}else if(dataGrid.getSort().equals("FModel")){

					sql += " order by t1.Fmodel  "+dataGrid.getOrder();

				
			}else if(dataGrid.getSort().equals("FWidth")){

					sql += " order by FWidth "+dataGrid.getOrder();

			}else if(dataGrid.getSort().equals("FBatchNo")){

					sql += " order by u1.FBatchNo "+dataGrid.getOrder();

			}else if(dataGrid.getSort().equals("FStockName")){

					sql += " order by t1.FName "+dataGrid.getOrder();

				
			}else if(dataGrid.getSort().equals("FStockPlace")){

					sql += " order by t5.FName "+dataGrid.getOrder();

				
			}else if(dataGrid.getSort().equals("FQty")){

					sql += " order by FQty "+dataGrid.getOrder();

				
			}
			
		}
		
		
		List<Map<String, Object>> maplist = null;
		List<Map<String, Object>> allmaplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());

		} catch (Exception ex)
		{

		}
		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
		// 数量合计
		BigDecimal sum_qty = new BigDecimal("0.0000");
		for (Map<String, Object> map : maplist)
		{
		   System.out.println(map.get("FQty"));
		   sum_qty = sum_qty.add((BigDecimal) map.get("FQty"));
		}
		dataGrid.setFooter("FUintName:合计" + ",FQty:" + sum_qty);
		TagUtil.datagrid(response, dataGrid);
	}

	/**
	 * easyui 原材料即时库存
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "y_datagrid")
	public void y_datagrid(ProcessReportEntity processReport, HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{

		String sql = " Select t1.FName as FItemName,t1.FModel,u1.FBatchNo, ";
		sql += " t2.FName as FStockName,t5.FName as FStockPlace,t4.FName as FUintName, ";
		sql += " Convert(decimal(18,4),u1.FQty/t4.FCoefficient) as FQty,t1.FNumber AS FItemNumber,t9.FName AS FWidth ";
		sql += " From RemoteServer.AIS2009.dbo.view_inventory u1 ";
		sql += " left join RemoteServer.AIS2009.dbo.t_ICItem t1 on u1.FItemID = t1.FItemID ";
		sql += " left join RemoteServer.AIS2009.dbo.t_Stock t2 on u1.FStockID=t2.FItemID ";
		sql += " left join RemoteServer.AIS2009.dbo.t_MeasureUnit t4 on t1.FStoreUnitID=t4.FMeasureUnitID ";
		sql += " left join RemoteServer.AIS2009.dbo.t_StockPlace t5 on u1.FStockPlaceID=t5.FSPID ";
		sql += " left join RemoteServer.AIS2009.dbo.t_AuxItem t9 on u1.FAuxPropID=t9.FItemID ";
		sql += " where (Round(u1.FQty,t1.FQtyDecimal)<>0 OR Round(u1.FQty/t4.FCoefficient,t1.FQtyDecimal)<>0) ";
		sql += " and t1.FDeleted=0  ";
	 // sql += " AND t2.FItemID in (557,10432,11097,559) ";
		sql += " AND t2.FItemID in (557,558,559,560,561,572,7978,9397,10211,10429,10432,11017,11097) ";
		sql += " AND t2.FTypeID in (500,20291,20293) ";

		String FItemNumber = request.getParameter("FItemNumber");
		String FModel = request.getParameter("FModel");
		String FWidth = request.getParameter("FWidth");
		String FItemName = request.getParameter("FItemName");
		String FBatchNo = request.getParameter("FBatchNo");
		String FDCStockIDName = request.getParameter("FStockName");
		String FSPName = request.getParameter("FStockPlace");

		if (StringUtil.isNotEmpty(FItemNumber))
		{
			sql += " and t2.FNumber like " + FItemNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FModel))
		{
			sql += " and t1.Fmodel = " + "'" + FModel + "'";
		}
		if (StringUtil.isNotEmpty(FItemName))
		{
			sql += " and t1.Fname like " + "'%" + FItemName + "%'";
		}
		if (StringUtil.isNotEmpty(FWidth))
		{
			sql += " and t9.FName = " + "'" + FWidth + "'";
		}
		if (StringUtil.isNotEmpty(FBatchNo))
		{
			sql += " and u1.FBatchNo like " + "'%" + FBatchNo + "%'";
		}

		if (StringUtil.isNotEmpty(FDCStockIDName))
		{
			sql += " and t2.FName  = " + "'" + FDCStockIDName + "'";
		}
		if (StringUtil.isNotEmpty(FSPName))
		{
			sql += " and t5.FName  = " + "'" + FSPName + "'";
		}

		// 排序
		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){

					sql += " order by " + orderField +dataGrid.getOrder();

			}
		}
		

		List<Map<String, Object>> maplist = null;
		List<Map<String, Object>> allmaplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());

		} catch (Exception ex)
		{

		}
		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
		// 数量合计
		BigDecimal sum_qty = new BigDecimal("0.0000");
		for (Map<String, Object> map : maplist)
		{
		   sum_qty = sum_qty.add((BigDecimal) map.get("FQty"));
		}
		dataGrid.setFooter("FUintName:合计" + ",FQty:" + sum_qty);
		TagUtil.datagrid(response, dataGrid);
	}

	/**
	 * easyui 辅助材料即时库存
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "f_datagrid")
	public void f_datagrid(ProcessReportEntity processReport, HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{

		String sql = " Select t1.FName as FItemName,isnull(t1.FModel,'') FModel,t2.FName as FStockName, ";
		sql += " t4.FName as FUintName,Convert(decimal(18,4),u1.FQty/t4.FCoefficient) as FQty,t1.FNumber AS FItemNumber ";
		sql += " From RemoteServer.AIS2009.dbo.view_inventory u1 ";
		sql += " left join RemoteServer.AIS2009.dbo.t_ICItem t1 on u1.FItemID = t1.FItemID ";
		sql += " left join RemoteServer.AIS2009.dbo.t_Stock t2 on u1.FStockID=t2.FItemID ";
		sql += " left join RemoteServer.AIS2009.dbo.t_MeasureUnit t4 on t1.FStoreUnitID=t4.FMeasureUnitID ";
		sql += " left join RemoteServer.AIS2009.dbo.t_StockPlace t5 on u1.FStockPlaceID=t5.FSPID ";
		sql += " where (Round(u1.FQty,t1.FQtyDecimal)<>0 OR Round(u1.FQty/t4.FCoefficient,t1.FQtyDecimal)<>0) ";
		sql += " and t1.FDeleted=0  ";
		sql += " AND t2.FItemID in (564,565,566,567,568,562) ";
		sql += " AND t2.FTypeID in (500,20291,20293) ";

		String FItemNumber = request.getParameter("FItemNumber");
		String FItemName = request.getParameter("FItemName");
		String FDCStockIDName = request.getParameter("FStockName");

		if (StringUtil.isNotEmpty(FItemNumber))
		{
			sql += " and t2.FNumber like " + FItemNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FItemName))
		{
			sql += " and t1.Fname like " + "'%" + FItemName + "%'";
		}

		if (StringUtil.isNotEmpty(FDCStockIDName))
		{
			sql += " and t2.FName  = " + "'" + FDCStockIDName + "'";
		}

		// 排序
		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){
				sql += " order by " + orderField +dataGrid.getOrder();
			}
		}
		
		List<Map<String, Object>> maplist = null;
		List<Map<String, Object>> allmaplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());

		} catch (Exception ex)
		{

		}
		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
		// 数量合计
		BigDecimal sum_qty = new BigDecimal("0.0000");
		for (Map<String, Object> map : maplist)
		{
		   System.out.println(map.get("FQty"));
		   sum_qty = sum_qty.add((BigDecimal) map.get("FQty"));
		}
		dataGrid.setFooter("FUintName:合计" + ",FQty:" + sum_qty);
		TagUtil.datagrid(response, dataGrid);
	}
	
	
	
	/**
	 * easyui 产成品库存月报表
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "ccp_datagrid")
	public void ccp_datagrid(ProcessReportEntity processReport, HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{

		String sql = " select b.FName,b.FModel,Convert(decimal(18,4),sum(FBegQty)) AS SQJC,Convert(decimal(18,4),sum(FReceive)) AS BQSC,Convert(decimal(18,4),sum(FSend)) AS BQXS,Convert(decimal(18,4),sum(FEndQty)) AS QMJC ";
		sql += " from RemoteServer.AIS2009.dbo.ICInvBal a inner join RemoteServer.AIS2009.dbo.t_icitem b on a.FItemID = b.FItemID ";
		sql += " where 1=1  "; 

		String FDate = request.getParameter("FDate");
		String FName = request.getParameter("FName");


		if (StringUtil.isNotEmpty(FName))
		{
			sql += " and b.Fname like " + "'%" + FName + "%'";
		}

		if (StringUtil.isNotEmpty(FDate))
		{
			sql += " and a.FYear = Year('" + FDate+"-01" + "') and a.FPeriod = MONTH('" + FDate+"-01"+ "')";
		}
       
		sql += " group by b.FModel,b.FName ";
		// 排序
/*		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){
				if(dataGrid.getOrder() == SortDirection.asc){
					sql += " order by " + orderField + " asc";
				}else{
					sql += " order by " + orderField + " desc";
				}
			}
		}*/
		
		List<Map<String, Object>> maplist = null;
		List<Map<String, Object>> allmaplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());

		} catch (Exception ex)
		{

		}
		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
		// 数量合计
		BigDecimal sum_qty = new BigDecimal("0.0000");
		BigDecimal sum_qty2 = new BigDecimal("0.0000");
		BigDecimal sum_qty3 = new BigDecimal("0.0000");
		BigDecimal sum_qty4 = new BigDecimal("0.0000");
		for (Map<String, Object> map : maplist)
		{
		  sum_qty = sum_qty.add((BigDecimal) map.get("SQJC"));
		   sum_qty2 = sum_qty.add((BigDecimal) map.get("BQSC"));
		   sum_qty3 = sum_qty.add((BigDecimal) map.get("BQXS"));
		   sum_qty4 = sum_qty.add((BigDecimal) map.get("QMJC"));
		   
		}
		dataGrid.setFooter("FName:合计" + ",SQJC:" + sum_qty + ",BQSC:" + sum_qty2  + ",BQXS:" +sum_qty3 + ",QMJC:" + sum_qty4);
		TagUtil.datagrid(response, dataGrid);
	}
    
	
	
	/**
	 * easyui 产成品入库月报表
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "inStock_datagrid")
	public void inStock_datagrid(ProcessReportEntity processReport, HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{
    
		String FDate = request.getParameter("FDate");
		String FName = request.getParameter("FName");
		String sql = " SELECT T3.FName,t3.FModel,SUM(Convert(decimal(18,4),T2.FQty)) AS QTY  ";
		sql += "  FROM RemoteServer.AIS2009.dbo.ICStockBill T1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry T2 ON T1.FInterID=T2.FInterID AND T1.FTranType=2  ";
			sql += "  inner join RemoteServer.AIS2009.dbo.t_icitem T3 on T3.FItemID = T2.FItemID where 1=1 ";
			if (StringUtil.isNotEmpty(FName))
			{
				sql += " and t3.FName like '%" + FName + "%'";
			}
			if (StringUtil.isNotEmpty(FDate))
			{
				sql += " and Year(t1.FDate) = Year('" + FDate+"-01" + "') and Month(t1.FDate) = MONTH('" + FDate+"-01"+ "')";
			}
					sql += "  GROUP BY T2.FItemID,t3.FName,t3.FModel  ";

	

		// 排序
/*		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){
				if(dataGrid.getOrder() == SortDirection.asc){
					sql += " order by " + orderField + " asc";
				}else{
					sql += " order by " + orderField + " desc";
				}
			}
		}*/
		
		List<Map<String, Object>> maplist = null;
		List<Map<String, Object>> allmaplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());

		} catch (Exception ex)
		{

		}
		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
	// 数量合计
		BigDecimal sum_qty = new BigDecimal("0.0000");
		for (Map<String, Object> map : maplist)
		{
		   sum_qty = sum_qty.add((BigDecimal) map.get("QTY"));
		}
		dataGrid.setFooter("FName:合计" + ",QTY:" + sum_qty);
		TagUtil.datagrid(response, dataGrid);
	}
	
	
	
	
	
	/**
	 * easyui 机组耗用明细月报表
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "jzhy_datagrid")
	public void jzhy_datagrid(ProcessReportEntity processReport, HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{
            
		    String FDate = request.getParameter("FDate");
		    
	        String sql = " select ";
			sql += " FUse, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '标准件' THEN FAmount ELSE 0 END) AS BZJL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '低值类' THEN FAmount ELSE 0 END) AS DZ, ";
			sql += "  MAX(CASE FEntrySelfB0444 WHEN '电器' THEN FAmount ELSE 0 END) AS DQL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '工量具' THEN FAmount ELSE 0 END) AS GLJL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '辊类' THEN FAmount ELSE 0 END) AS HL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '化工' THEN FAmount ELSE 0 END) AS HGL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '机械' THEN FAmount ELSE 0 END) AS JXL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '其他' THEN FAmount ELSE 0 END) AS QTL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '套筒' THEN FAmount ELSE 0 END) AS TTL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '油脂' THEN FAmount ELSE 0 END) AS YZL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '专用备件' THEN FAmount ELSE 0 END) AS ZYPJ, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '劳护类' THEN FAmount ELSE 0 END) AS LBYP, ";
			sql += "  MAX(CASE FEntrySelfB0444 WHEN '管理类' THEN FAmount ELSE 0 END) AS BGYPL ";
			sql += " FROM ( ";
			sql += " SELECT  t1.FUse,t2.FEntrySelfB0444,Convert(decimal(18,4),sum(t2.FAmount)) as FAmount ";
			sql += "  FROM RemoteServer.AIS2009.dbo.ICStockBill T1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry T2 ON T1.FInterID=T2.FInterID  ";
			sql += " WHERE T1.FTranType=24 AND  len(RTRIM(FUse)) > 0   ";
			if(StringUtil.isNotEmpty(FDate)){
				sql += " and Year(t1.FDate) = Year('" + FDate+"-01" + "') and Month(t1.FDate) = MONTH('" + FDate+"-01"+ "')";
			}
			
			sql += " group by t1.FUse,t2.FEntrySelfB0444 ";
			
			sql += " ) t ";
			sql += " GROUP BY FUse ";
		
			

		// 排序
/*		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){
				if(dataGrid.getOrder() == SortDirection.asc){
					sql += " order by " + orderField + " asc";
				}else{
					sql += " order by " + orderField + " desc";
				}
			}
		}*/
		
		List<Map<String, Object>> maplist = null;
		List<Map<String, Object>> allmaplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());

		} catch (Exception ex)
		{

		}
		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
	   // 数量合计
		BigDecimal sum_qty = new BigDecimal("0.0000");
		BigDecimal sum_qty2 = new BigDecimal("0.0000");
		BigDecimal sum_qty3 = new BigDecimal("0.0000");
		BigDecimal sum_qty4 = new BigDecimal("0.0000");
		BigDecimal sum_qty5 = new BigDecimal("0.0000");
		BigDecimal sum_qty6 = new BigDecimal("0.0000");
		BigDecimal sum_qty7 = new BigDecimal("0.0000");
		BigDecimal sum_qty8 = new BigDecimal("0.0000");
		BigDecimal sum_qty9 = new BigDecimal("0.0000");
		BigDecimal sum_qty10 = new BigDecimal("0.0000");
		BigDecimal sum_qty11 = new BigDecimal("0.0000");
		BigDecimal sum_qty12 = new BigDecimal("0.0000");
		BigDecimal sum_qty13 = new BigDecimal("0.0000");
		
		for (Map<String, Object> map : maplist)
		{
		   sum_qty = sum_qty.add((BigDecimal) map.get("BZJL"));
		   sum_qty2 = sum_qty2.add((BigDecimal) map.get("DZ"));
		   sum_qty3 = sum_qty3.add((BigDecimal) map.get("DQL"));
		   sum_qty4 = sum_qty4.add((BigDecimal) map.get("GLJL"));
		   sum_qty5 = sum_qty5.add((BigDecimal) map.get("HL"));
		   sum_qty6 = sum_qty6.add((BigDecimal) map.get("HGL"));
		   sum_qty7 = sum_qty7.add((BigDecimal) map.get("JXL"));
		   sum_qty8 = sum_qty8.add((BigDecimal) map.get("QTL"));
		   sum_qty9 = sum_qty9.add((BigDecimal) map.get("TTL"));
		   sum_qty10 = sum_qty10.add((BigDecimal) map.get("YZL"));
		   sum_qty11 = sum_qty11.add((BigDecimal) map.get("ZYPJ"));
		   sum_qty12 = sum_qty12.add((BigDecimal) map.get("LBYP"));
		   sum_qty13 = sum_qty13.add((BigDecimal) map.get("BGYPL"));
		}
		dataGrid.setFooter("FUse:合计" + ",BZJL:" + sum_qty+ ",DZ:" + sum_qty2+ ",DQL:" + sum_qty3+ ",GLJL:" + sum_qty4+ ",HL:"
		+ sum_qty5+ ",HGL:" + sum_qty6+ ",JXL:" + sum_qty7+ ",QTL:" + sum_qty8+ ",TTL:" + sum_qty9+ ",YZL:" + sum_qty10+ ",ZYPJ:" + sum_qty11+ ",LBYP:" + sum_qty12+ ",BGYPL:" + sum_qty13);
		TagUtil.datagrid(response, dataGrid);
	}
	
	
	

	/**
	 * 
	 * 導出   机组耗用明细
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export_jzhyList")
	public void export(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

		
		  String FDate = request.getParameter("FDate");
		    
	        String sql = " select ";
			sql += " FUse, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '标准件' THEN FAmount ELSE 0 END) AS BZJL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '低值类' THEN FAmount ELSE 0 END) AS DZ, ";
			sql += "  MAX(CASE FEntrySelfB0444 WHEN '电器' THEN FAmount ELSE 0 END) AS DQL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '工量具' THEN FAmount ELSE 0 END) AS GLJL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '辊类' THEN FAmount ELSE 0 END) AS HL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '化工' THEN FAmount ELSE 0 END) AS HGL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '机械' THEN FAmount ELSE 0 END) AS JXL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '其他' THEN FAmount ELSE 0 END) AS QTL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '套筒' THEN FAmount ELSE 0 END) AS TTL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '油脂' THEN FAmount ELSE 0 END) AS YZL, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '专用备件' THEN FAmount ELSE 0 END) AS ZYPJ, ";
			sql += " MAX(CASE FEntrySelfB0444 WHEN '劳护类' THEN FAmount ELSE 0 END) AS LBYP, ";
			sql += "  MAX(CASE FEntrySelfB0444 WHEN '管理类' THEN FAmount ELSE 0 END) AS BGYPL ";
			sql += " FROM ( ";
			sql += " SELECT  t1.FUse,t2.FEntrySelfB0444,Convert(decimal(18,4),sum(t2.FAmount)) as FAmount ";
			sql += "  FROM RemoteServer.AIS2009.dbo.ICStockBill T1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry T2 ON T1.FInterID=T2.FInterID  ";
			sql += " WHERE T1.FTranType=24 AND  len(RTRIM(FUse)) > 0   ";
			if(StringUtil.isNotEmpty(FDate)){
				sql += " and Year(t1.FDate) = Year('" + FDate+"-01" + "') and Month(t1.FDate) = MONTH('" + FDate+"-01"+ "')";
			}
			
			sql += " group by t1.FUse,t2.FEntrySelfB0444 ";
			
			sql += " ) t ";
			sql += " GROUP BY FUse ";
		
			

		List<Map<String, Object>> maplist = null;

		try {
			maplist = systemService.findForJdbc(sql);
			List<String[]> list = new ArrayList<String[]>();

			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FUse");  
				arr[1] =  String.valueOf((BigDecimal) map.get("BZJL"));
				arr[2] = String.valueOf((BigDecimal) map.get("DZ"));
				arr[3] =  String.valueOf((BigDecimal) map.get("DQL"));
				arr[4] =  String.valueOf((BigDecimal) map.get("GLJL"));
				arr[5] =  String.valueOf((BigDecimal) map.get("HL"));
				arr[6] =   String.valueOf((BigDecimal) map.get("HGL"));
				arr[7] =   String.valueOf((BigDecimal) map.get("JXL"));
				arr[8] =  String.valueOf((BigDecimal) map.get("QTL"));
				arr[9] =  String.valueOf((BigDecimal) map.get("TTL"));
				arr[10] = String.valueOf((BigDecimal) map.get("YZL"));
				arr[11] = String.valueOf((BigDecimal) map.get("ZYPJ"));
				arr[12] =  String.valueOf((BigDecimal) map.get("LBYP"));
				arr[13] = String.valueOf((BigDecimal) map.get("BGYPL"));
				list.add(arr);

			}
            
			   // 数量合计
			BigDecimal sum_qty = new BigDecimal("0.0000");
			BigDecimal sum_qty2 = new BigDecimal("0.0000");
			BigDecimal sum_qty3 = new BigDecimal("0.0000");
			BigDecimal sum_qty4 = new BigDecimal("0.0000");
			BigDecimal sum_qty5 = new BigDecimal("0.0000");
			BigDecimal sum_qty6 = new BigDecimal("0.0000");
			BigDecimal sum_qty7 = new BigDecimal("0.0000");
			BigDecimal sum_qty8 = new BigDecimal("0.0000");
			BigDecimal sum_qty9 = new BigDecimal("0.0000");
			BigDecimal sum_qty10 = new BigDecimal("0.0000");
			BigDecimal sum_qty11 = new BigDecimal("0.0000");
			BigDecimal sum_qty12 = new BigDecimal("0.0000");
			BigDecimal sum_qty13 = new BigDecimal("0.0000");
			
			for (Map<String, Object> map : maplist)
			{
			   sum_qty = sum_qty.add((BigDecimal) map.get("BZJL"));
			   sum_qty2 = sum_qty2.add((BigDecimal) map.get("DZ"));
			   sum_qty3 = sum_qty3.add((BigDecimal) map.get("DQL"));
			   sum_qty4 = sum_qty4.add((BigDecimal) map.get("GLJL"));
			   sum_qty5 = sum_qty5.add((BigDecimal) map.get("HL"));
			   sum_qty6 = sum_qty6.add((BigDecimal) map.get("HGL"));
			   sum_qty7 = sum_qty7.add((BigDecimal) map.get("JXL"));
			   sum_qty8 = sum_qty8.add((BigDecimal) map.get("QTL"));
			   sum_qty9 = sum_qty9.add((BigDecimal) map.get("TTL"));
			   sum_qty10 = sum_qty10.add((BigDecimal) map.get("YZL"));
			   sum_qty11 = sum_qty11.add((BigDecimal) map.get("ZYPJ"));
			   sum_qty12 = sum_qty12.add((BigDecimal) map.get("LBYP"));
			   sum_qty13 = sum_qty13.add((BigDecimal) map.get("BGYPL"));
			}
			
			String[] sums = new String[]{"合计",String.valueOf(sum_qty),String.valueOf(sum_qty2),String.valueOf(sum_qty3),String.valueOf(sum_qty4),String.valueOf(sum_qty5),String.valueOf(sum_qty6),String.valueOf(sum_qty7),String.valueOf(sum_qty8),
					String.valueOf(sum_qty9),String.valueOf(sum_qty10),String.valueOf(sum_qty11),String.valueOf(sum_qty12),String.valueOf(sum_qty13)};
			list.add(sums);
			String[] columnNames = new String[] { "部门", "标准件", "低值类", "电器", "工量具", "辊类", "化工", "机械", "其他",
					"套筒", "油脂", "专用备件", "劳护类", "管理类" };
			String titleName = "机组耗用明细.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

			ExcelUtils.create(request, list, columnNames, titleName, fOut);

		} catch (Exception ex) {

		}

	}
	
	/**
	 * easyui 专用备品备件购入耗用结存月报表
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "zybpbj_datagrid")
	public void zybpbj_datagrid(ProcessReportEntity processReport, HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{
            
		  String FDate = request.getParameter("FDate");
		    
		    	
    	String sql="	select t2.FName,t3.FName as FUintName,case when FBegQty <  1 then 0 else Convert(decimal(18,4),FBegBal/FBegQty) end AS FPrice,Convert(decimal(18,4),t1.FBegQty) as FBegQty,Convert(decimal(18,4),FBegBal) as FBegBal, ";
    	sql+="	Convert(decimal(18,4),FReceive) as FReceive,Convert(decimal(18,4),FDebit) as FDebit,Convert(decimal(18,4),FSend) as FSend, ";
    	sql+="	Convert(decimal(18,4),FCredit) as FCredit,Convert(decimal(18,4),FEndQty) as FEndQty,Convert(decimal(18,4),FEndBal) as FEndBal ";
    	sql+="	from RemoteServer.AIS2009.dbo.ICInvBal t1 inner join RemoteServer.AIS2009.dbo.t_icitem t2 on t1.FItemID = t2.FItemID ";
    	sql+="	 inner join RemoteServer.AIS2009.dbo.t_MeasureUnit t3 on t2.FUnitID = t3.FMeasureUnitID ";
    	
     	sql += "  where FTypeID = 40056  ";
		
    	if(StringUtil.isNotEmpty(FDate)){
			sql += " and t1.FYear = Year('" + FDate+"-01" + "') and t1.FPeriod = MONTH('" + FDate+"-01"+ "')";
		}

		
		List<Map<String, Object>> maplist = null;
		List<Map<String, Object>> allmaplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());

		} catch (Exception ex)
		{

		}
		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
	   // 数量合计
		BigDecimal sum_qty = new BigDecimal("0.0000");
		BigDecimal sum_qty2 = new BigDecimal("0.0000");
		BigDecimal sum_qty3 = new BigDecimal("0.0000");
		BigDecimal sum_qty4 = new BigDecimal("0.0000");
		BigDecimal sum_qty5 = new BigDecimal("0.0000");
		BigDecimal sum_qty6 = new BigDecimal("0.0000");
		BigDecimal sum_qty7 = new BigDecimal("0.0000");
		BigDecimal sum_qty8 = new BigDecimal("0.0000");
		
		for (Map<String, Object> map : maplist)
		{
		   sum_qty = sum_qty.add((BigDecimal) map.get("FBegQty"));
		   sum_qty2 = sum_qty2.add((BigDecimal) map.get("FBegBal"));
		   sum_qty3 = sum_qty3.add((BigDecimal) map.get("FReceive"));
		   sum_qty4 = sum_qty4.add((BigDecimal) map.get("FDebit"));
		   sum_qty5 = sum_qty5.add((BigDecimal) map.get("FSend"));
		   sum_qty6 = sum_qty6.add((BigDecimal) map.get("FCredit"));
		   sum_qty7 = sum_qty7.add((BigDecimal) map.get("FEndQty"));
		   sum_qty8 = sum_qty8.add((BigDecimal) map.get("FEndBal"));
		}
		dataGrid.setFooter("FName:合计" + ",FBegQty:" + sum_qty+ ",FBegBal:" + sum_qty2+ ",FReceive:" + sum_qty3+ ",FDebit:" + sum_qty4+ ",FSend:" + sum_qty5+ ",FCredit:" + sum_qty6+ ",FEndQty:" + sum_qty7+ ",FEndBal:" + sum_qty8);
		TagUtil.datagrid(response, dataGrid);
	}
	
	
	/**
	 * 
	 * 導出   专用备品备件  耗用明细
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export_zyjhc")
	public void export_zyjhc(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {
		
	     
			  String FDate = request.getParameter("FDate");
			    
			  //String sql = "  select t2.FName,t3.FName as FUintName,case when FBegQty < 1 then 0 else Convert(decimal(18,4),FBegBal/FBegQty) end AS FPrice,Convert(decimal(18,4),t1.FBegQty) as FBegQty,Convert(decimal(18,4),FBegBal) as FBegBal, ";
			   
			  String sql = " select t2.FName,t3.FName as FUintName,case when FBegQty < 1 then 0 else Convert(decimal(18,4),FBegBal/FBegQty) end AS FPrice,Convert(decimal(18,4),t1.FBegQty) as FBegQty,Convert(decimal(18,4),FBegBal) as FBegBal, ";
			  sql += " Convert(decimal(18,4),FReceive) as FReceive,Convert(decimal(18,4),FDebit) as FDebit,Convert(decimal(18,4),FSend) as FSend, ";
				   sql += " Convert(decimal(18,4),FCredit) as FCredit,Convert(decimal(18,4),FEndQty) as FEndQty,Convert(decimal(18,4),FEndBal) as FEndBal ";
					   sql += " from RemoteServer.AIS2009.dbo.ICInvBal t1 inner join RemoteServer.AIS2009.dbo.t_icitem t2 on t1.FItemID = t2.FItemID ";
						   sql += " inner join RemoteServer.AIS2009.dbo.t_MeasureUnit t3 on t2.FUnitID = t3.FMeasureUnitID ";
			    		
			    	
			    	sql += "  where FTypeID = 40056  ";
				
			    	if(StringUtil.isNotEmpty(FDate)){
						sql += " and t1.FYear = Year('" + FDate+"-01" + "') and t1.FPeriod = MONTH('" + FDate+"-01"+ "')";
					}
			    	
					List<Map<String, Object>> maplist = null;

					try {
						maplist = systemService.findForJdbc(sql);
						List<String[]> list = new ArrayList<String[]>();

						for (Map<String, Object> map : maplist) {
							String[] arr = new String[map.size()];
							arr[0] = (String) map.get("FName");  
							arr[1] =  String.valueOf((BigDecimal) map.get("FPrice"));
							arr[2] = String.valueOf((BigDecimal) map.get("FBegQty"));
							arr[3] =  String.valueOf((BigDecimal) map.get("FBegBal"));
							arr[4] =  String.valueOf((BigDecimal) map.get("FReceive"));
							arr[5] =  String.valueOf((BigDecimal) map.get("FDebit"));
							arr[6] =   String.valueOf((BigDecimal) map.get("FSend"));
							arr[7] =   String.valueOf((BigDecimal) map.get("FCredit"));
							arr[8] =  String.valueOf((BigDecimal) map.get("FEndQty"));
							arr[9] =  String.valueOf((BigDecimal) map.get("FEndBal"));
							list.add(arr);

						}
			            
						   // 数量合计
						BigDecimal sum_qty = new BigDecimal("0.0000");
						BigDecimal sum_qty2 = new BigDecimal("0.0000");
						BigDecimal sum_qty3 = new BigDecimal("0.0000");
						BigDecimal sum_qty4 = new BigDecimal("0.0000");
						BigDecimal sum_qty5 = new BigDecimal("0.0000");
						BigDecimal sum_qty6 = new BigDecimal("0.0000");
						BigDecimal sum_qty7 = new BigDecimal("0.0000");
						BigDecimal sum_qty8 = new BigDecimal("0.0000");
						
						for (Map<String, Object> map : maplist)
						{
							  sum_qty = sum_qty.add((BigDecimal) map.get("FBegQty"));
							   sum_qty2 = sum_qty2.add((BigDecimal) map.get("FBegBal"));
							   sum_qty3 = sum_qty3.add((BigDecimal) map.get("FReceive"));
							   sum_qty4 = sum_qty4.add((BigDecimal) map.get("FDebit"));
							   sum_qty5 = sum_qty5.add((BigDecimal) map.get("FSend"));
							   sum_qty6 = sum_qty6.add((BigDecimal) map.get("FCredit"));
							   sum_qty7 = sum_qty7.add((BigDecimal) map.get("FEndQty"));
							   sum_qty8 = sum_qty8.add((BigDecimal) map.get("FEndBal"));
						}
						
						String[] sums = new String[]{"合计","",String.valueOf(sum_qty),String.valueOf(sum_qty2),String.valueOf(sum_qty3),String.valueOf(sum_qty4),String.valueOf(sum_qty5),String.valueOf(sum_qty6),String.valueOf(sum_qty7),String.valueOf(sum_qty8)};
						list.add(sums);
						String[] columnNames = new String[] { "物料名称", "期初结存单价", "期初结存数量", "期初结存金额", "收入数量", "收入金额", "发出数量", "发出金额", "结存数量",
								"结存金额"};
						String titleName = "专用备品备件进耗存.xls";

						OutputStream fOut = response.getOutputStream();
						response.setHeader("Content-Disposition", "attachment; filename="
								+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

						ExcelUtils.create(request, list, columnNames, titleName, fOut);

					} catch (Exception ex) {

					}	    	
		
	}
}
